﻿using System;
using System.Configuration;
using System.Data;
using MySql.Data.MySqlClient;
using System.IO;
using AssettDatabase.Common;

namespace AssettDatabase.DataAccess
{
    class DAO
    {
        private MySqlConnection mConnection;
        private MySqlCommand mSqlCommand;

        public string Server { get; set; }
        public string DataBase { get; set; }
        public string User { get; set; }
        public string Pwd{ get; set; }
        public string FilePath { get; set; }
        public string FirstRun { get; set; }
        public DAO()
        {
            mConnection = new MySqlConnection();
            mSqlCommand = new MySqlCommand();
        }

        public void GetConnectionString()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            MySqlConnectionStringBuilder sqlConn = new MySqlConnectionStringBuilder(ConnectionString);

            Server = sqlConn.Server;
            DataBase = sqlConn.Database;
            User = sqlConn.UserID;
            Pwd = sqlConn.Password;
            FilePath = ConfigurationManager.AppSettings["FilePath"].ToString();
            FirstRun = ConfigurationManager.AppSettings["FirstRun"].ToString();
        }

        public void SetConnectionString()
        {
            MySqlConnectionStringBuilder sqlConn = new MySqlConnectionStringBuilder();
            sqlConn.Server = Server;
            sqlConn.Database = DataBase;
            sqlConn.UserID = User;
            sqlConn.Password = Pwd;

            Configuration AppConfig = ConfigurationManager.OpenExeConfiguration(ConfigurationUserLevel.None);
            AppConfig.AppSettings.Settings["FilePath"].Value = FilePath;
            AppConfig.AppSettings.Settings["FirstRun"].Value = FirstRun;
            AppConfig.ConnectionStrings.ConnectionStrings["ConnectionString"].ConnectionString = sqlConn.ConnectionString;
            AppConfig.Save(ConfigurationSaveMode.Modified);
            ConfigurationManager.RefreshSection("appSettingsappSettings");
            ConfigurationManager.RefreshSection("connectionStrings");
        }

        public MySqlConnection OpenConnection()
        {
            try
            {
                if (mConnection.State == System.Data.ConnectionState.Closed || mConnection.State == System.Data.ConnectionState.Broken)
                {
                    mConnection.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                    mConnection.Open();
                }
                return mConnection;
            }
            catch (MySqlException ex)
            {
                switch(ex.ErrorCode)
                {
                    case -2147467259:
                        throw new CustomException("Authentication details are incorrect!", ex);
                    default:
                        throw new CustomException("Unspecified error", ex);
                }
            }
        }

        public void CloseConnection()
        {
            if (mConnection.State == System.Data.ConnectionState.Open)
            {
                mConnection.Close();
            }
        }

        public void ExecuteNonQuery(string sqltext)
        {
            try
            {
                mSqlCommand.Connection = OpenConnection();
                mSqlCommand.CommandText = sqltext;
                mSqlCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
                mSqlCommand = null;
            }
        }

        public long ExecuteNonQueryWithID(string sqltext)
        {
            try
            {
                mSqlCommand.Connection = OpenConnection();
                mSqlCommand.CommandText = sqltext;
                mSqlCommand.ExecuteNonQuery();

                return mSqlCommand.LastInsertedId;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
                mSqlCommand = null;
            }
        }

        public DataTable ExecuteSelectQuery(string sqltext)
        {
            try
            {
                MySqlDataAdapter mSqlDataAdapter = new MySqlDataAdapter();
                DataSet ds = new DataSet();
                mSqlCommand.Connection = OpenConnection();
                mSqlCommand.CommandText = sqltext;
                mSqlCommand.ExecuteNonQuery();
                mSqlDataAdapter.SelectCommand = mSqlCommand;
                mSqlDataAdapter.Fill(ds);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                CloseConnection();
                mSqlCommand = null;
            }
        }

        public void AddParameter(string name, object value)
        {
            MySqlParameter param = new MySqlParameter();

            param.ParameterName = name;
            param.Value = value;
            mSqlCommand.Parameters.Add(param);
        }

        public void CreateSchema(string _server, string _user, string _pwd, string _db)
        {
            try
            {
                string sCREATESCHEMA = @"   SET SQL_MODE = ""NO_AUTO_VALUE_ON_ZERO"";
                                            SET time_zone = ""+00:00"";

                                            /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
                                            /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
                                            /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
                                            /*!40101 SET NAMES utf8 */;

                                            DROP TABLE IF EXISTS `Assets`;
                                            CREATE TABLE IF NOT EXISTS `Assets` (
                                                `Asset_ID` int(100) NOT NULL,
                                                `Customer_ID` int(11) NOT NULL,
                                                `Serial_Number` varchar(100) NOT NULL,
                                                `Model_Number` varchar(100) NOT NULL,
                                                `Asset_Type_ID` varchar(100) NOT NULL,
                                                `Vendor_Name` varchar(100) NOT NULL,
                                                `A_S_Purchase_Date` date DEFAULT NULL,
                                                `Sold_To_Customer_Date` date DEFAULT NULL,
                                                `A_S_Invoice_Number` varchar(100) DEFAULT NULL,
                                                `Warranty_Expiry` date DEFAULT NULL,
                                                `Software_Renewal_Date` date DEFAULT NULL,
                                                `Supplier_ID` int(11) NOT NULL,
                                                `Supplier_Invoice_Number` varchar(100) DEFAULT NULL,
                                                `Asset_Notes` blob NOT NULL,
                                                `Asset_Status` smallint(6) DEFAULT '1',
                                                `Asset_InStock` smallint(6) DEFAULT '0'
                                            ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

                                            --
                                            -- Table structure for table `Asset_Images`
                                            --

                                            DROP TABLE IF EXISTS `Asset_Images`;
                                            CREATE TABLE IF NOT EXISTS `Asset_Images` (
                                                `Image_ID` int(11) NOT NULL,
                                                `Asset_ID` int(11) NOT NULL,
                                                `FileName` varchar(255) NOT NULL
                                            ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

                                            --
                                            -- Table structure for table `Asset_Type`
                                            --

                                            DROP TABLE IF EXISTS `Asset_Type`;
                                            CREATE TABLE IF NOT EXISTS `Asset_Type` (
                                                `Asset_Type_ID` int(100) NOT NULL,
                                                `Asset_Type_Name` varchar(100) NOT NULL,
                                                `Asset_Type_Status` smallint(6) NOT NULL DEFAULT '1'
                                            ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

                                            --
                                            -- Table structure for table `Customer`
                                            --

                                            DROP TABLE IF EXISTS `Customer`;
                                            CREATE TABLE IF NOT EXISTS `Customer` (
                                                `Customer_Name` varchar(100) NOT NULL,
                                                `Customer_Phone` text NOT NULL,
                                                `Customer_Mobile` text NOT NULL,
                                                `Primary_Contact_FN` varchar(16) NOT NULL,
                                                `Primary_Contact_LN` varchar(16) NOT NULL,
                                                `Primary_Contact_Email` varchar(100) NOT NULL,
                                                `Customer_ID` int(6) NOT NULL,
                                                `Customer_Website` text NOT NULL,
                                                `Customer_ABN` varchar(12) NOT NULL,
                                                `Accounts_Payable_FN` varchar(16) NOT NULL,
                                                `Accounts_Payable_LN` varchar(16) NOT NULL,
                                                `Accounts_Payable_EML` varchar(100) NOT NULL,
                                                `Customer_Notes` mediumblob NOT NULL,
                                                `Customer_Status` smallint(6) NOT NULL DEFAULT '1'
                                            ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

                                            --
                                            -- Table structure for table `Suppliers`
                                            --

                                            DROP TABLE IF EXISTS `Suppliers`;
                                            CREATE TABLE IF NOT EXISTS `Suppliers` (
                                                `Supplier_Name` varchar(20) NOT NULL,
                                                `Supplier_Phone` varchar(20) NOT NULL,
                                                `Sup_Primary_Contact_FN` varchar(20) NOT NULL,
                                                `Sup_Primary_Contact_LN` varchar(20) NOT NULL,
                                                `Sup_Primary_Contact_EML` varchar(100) NOT NULL,
                                                `Supplier_Website` varchar(100) NOT NULL,
                                                `Sup_Web_Username` varchar(100) NOT NULL,
                                                `Sup_Web_Password` varchar(100) NOT NULL,
                                                `Supplier_Customer_Code` varchar(100) NOT NULL,
                                                `Supplier_ID` int(10) NOT NULL,
                                                `Sup_Comments` blob NOT NULL,
                                                `Supplier_Status` smallint(6) NOT NULL DEFAULT '1'
                                            ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

                                            --
                                            -- Table structure for table `Vendors`
                                            --

                                            DROP TABLE IF EXISTS `Vendors`;
                                            CREATE TABLE IF NOT EXISTS `Vendors` (
                                                `Vendor_ID` int(10) NOT NULL,
                                                `Vendor_Name` varchar(100) NOT NULL,
                                                `Vendor_Support_Email` varchar(60) NOT NULL,
                                                `Vendor_Support_Phone` varchar(100) NOT NULL,
                                                `Vendor_Notes` tinyblob NOT NULL,
                                                `Vendor_Status` smallint(6) NOT NULL DEFAULT '1'
                                            ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

                                            ALTER TABLE `Assets`
                                                ADD PRIMARY KEY (`Asset_ID`), ADD UNIQUE KEY `Serial_Number` (`Serial_Number`), ADD KEY `Customer_ID` (`Customer_ID`), ADD KEY `Asset_Type` (`Asset_Type_ID`), ADD KEY `Vendor_Name` (`Vendor_Name`), ADD KEY `Supplier` (`Supplier_ID`);

                                            ALTER TABLE `Asset_Images`
                                                ADD PRIMARY KEY (`Image_ID`);

                                            ALTER TABLE `Asset_Type`
                                                ADD PRIMARY KEY (`Asset_Type_ID`), ADD KEY `Asset_Type_Name` (`Asset_Type_Name`);

                                            ALTER TABLE `Customer`
                                                ADD PRIMARY KEY (`Customer_ID`), ADD UNIQUE KEY `Customer_Name` (`Customer_Name`), ADD UNIQUE KEY `Customer_Name_2` (`Customer_Name`), ADD KEY `Primary_Contact_ID` (`Primary_Contact_FN`,`Customer_ABN`,`Accounts_Payable_FN`), ADD KEY `Primary_Contact_FN` (`Primary_Contact_FN`), ADD KEY `Primary_Contact_ID_2` (`Primary_Contact_FN`);

                                            ALTER TABLE `Suppliers`
                                                ADD PRIMARY KEY (`Supplier_ID`), ADD UNIQUE KEY `Supplier_Name_2` (`Supplier_Name`), ADD KEY `Supplier_Name` (`Supplier_Name`);

                                            ALTER TABLE `Vendors`
                                                ADD PRIMARY KEY (`Vendor_ID`), ADD UNIQUE KEY `Vender_Name` (`Vendor_Name`);

                                            ALTER TABLE `Assets`
                                            MODIFY `Asset_ID` int(100) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

                                            ALTER TABLE `Asset_Images`
                                            MODIFY `Image_ID` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

                                            ALTER TABLE `Asset_Type`
                                            MODIFY `Asset_Type_ID` int(100) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

                                            ALTER TABLE `Customer`
                                            MODIFY `Customer_ID` int(6) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

                                            ALTER TABLE `Suppliers`
                                            MODIFY `Supplier_ID` int(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

                                            ALTER TABLE `Vendors`
                                            MODIFY `Vendor_ID` int(10) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=1;

                                            ALTER TABLE `Assets`
                                            ADD CONSTRAINT `Assets_ibfk_1` FOREIGN KEY (`Customer_ID`) REFERENCES `Customer` (`Customer_ID`),
                                            ADD CONSTRAINT `Assets_ibfk_2` FOREIGN KEY (`Vendor_Name`) REFERENCES `Vendors` (`Vendor_Name`),
                                            ADD CONSTRAINT `Assets_ibfk_3` FOREIGN KEY (`Supplier_ID`) REFERENCES `Suppliers` (`Supplier_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION;

                                            /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
                                            /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
                                            /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;";
                string sDROP = "DROP DATABASE IF EXISTS " + _db + ";";
                string sCREATE = "CREATE DATABASE " + _db + ";";
                MySqlConnectionStringBuilder sqlConn = new MySqlConnectionStringBuilder();
                sqlConn.Server = _server;
                sqlConn.UserID = _user;
                sqlConn.Password = _pwd;

                mConnection.ConnectionString = sqlConn.ConnectionString;
                mConnection.Open();
                mSqlCommand.Connection = mConnection;
                mSqlCommand.CommandText = sDROP;
                mSqlCommand.ExecuteNonQuery();
                mSqlCommand.CommandText = sCREATE;
                mSqlCommand.ExecuteNonQuery();
                mConnection.Close();

                ExecuteNonQuery(sCREATESCHEMA);
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
    }
}
